package net.gamebug.guessthemovie;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import java.util.ArrayList;
import java.util.List;


public class bd {
    
     private static final String DATABASE_NAME = "GuessTheMovie";
     private static final int DATABASE_VERSION = 1;
     private static final String TAG = "bd";

     private static final String TABLE_CREATE_MOVIES =
                "CREATE TABLE 'Movies' " +
                "('id' INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , 'idMovie' INTEGER NOT NULL , 'Name' VARCHAR NOT NULL , 'Image' VARCHAR NOT NULL );";
     
    private static final String TABLE_CREATE_SCORES =
                "CREATE TABLE 'Scores' " +
                "('id' INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , 'Name' VARCHAR NOT NULL , 'Score' INTEGER NOT NULL );";
          
     private final Context context;
     private DatabaseHelper DBHelper;
     private SQLiteDatabase db;
        
     public bd(Context ctx)
     {
        this.context = ctx;
        DBHelper = new DatabaseHelper(ctx);
     }
        
     private static class DatabaseHelper extends SQLiteOpenHelper
     {
        DatabaseHelper(Context context)
        {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }
                
        @Override
        public void onCreate(SQLiteDatabase db)
        {
            try 
            {
                db.execSQL(TABLE_CREATE_MOVIES);
                db.execSQL(TABLE_CREATE_SCORES);
                
                //inserts dos movies 
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('1','The Matrix','matrix1');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('1','The Matrix','matrix2');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('1','The Matrix','matrix3');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('2','Gladiator','gladiador1');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('2','Gladiator','gladiador2');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('2','Gladiator','gladiador3');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('3','The Godfather','godfather1');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('3','The Godfather','godfather2');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('3','The Godfather','godfather3');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('4','Twelve Monkeys','twelvemonkeys1');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('4','Twelve Monkeys','twelvemonkeys2');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('4','Twelve Monkeys','twelvemonkeys3');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('5','Star Wars','starwars1');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('5','Star Wars','starwars2');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('5','Star Wars','starwars3');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('6','Pulp Fiction','pulpfiction1');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('6','Pulp Fiction','pulpfiction2');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('6','Pulp Fiction','pulpfiction3');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('7','Indiana Jones','indianajones1');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('7','Indiana Jones','indianajones2');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('7','Indiana Jones','indianajones3');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('8','Braveheart','braveheart1');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('8','Braveheart','braveheart2');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('8','Braveheart','braveheart3');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('9','Pearl Harbor','pearlharbor1');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('9','Pearl Harbor','pearlharbor2');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('9','Pearl Harbor','pearlharbor3');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('10','Titanic','titanic1');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('10','Titanic','titanic2');");
                db.execSQL("INSERT INTO 'Movies' ('idMovie', 'Name', 'Image') VALUES ('10','Titanic','titanic3');");
                
                //inserts dos Scores
                db.execSQL("INSERT INTO 'SCORES' ('Name', 'Score') VALUES ('Sandra', '5000');");
                db.execSQL("INSERT INTO 'SCORES' ('Name', 'Score') VALUES ('Jessica', '4000');");
                db.execSQL("INSERT INTO 'SCORES' ('Name', 'Score') VALUES ('Carla', '3000');");
                db.execSQL("INSERT INTO 'SCORES' ('Name', 'Score') VALUES ('Cristina', '2000');");
                db.execSQL("INSERT INTO 'SCORES' ('Name', 'Score') VALUES ('Silvia', '1000');");
                
            } catch (SQLException e) 
            {
                e.printStackTrace();
                System.out.println("SQL EXCEPTION");
            }
        }
                
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");  
            db.execSQL("DROP TABLE IF EXISTS Movies;");
            onCreate(db);
        }
    }
        
    public bd open() throws SQLException
    {
        db = DBHelper.getWritableDatabase();
        return this;
    }
        
    public void close()
    {
        DBHelper.close();
    }
    
    // Methods of Scores
    
    public long insertPlayer(String name, long score)
    {
        ContentValues newScore = new ContentValues();
        newScore.put("Name", name);
        newScore.put("Score", score);
        return db.insert("Scores", null, newScore);
    }
    
    public Cursor getTop5Scores()
    {
        return db.rawQuery("SELECT Name, Score FROM Scores ORDER BY Score DESC LIMIT 5", null);
    }
    
    public List<String> getAllMovieNames()
    {
        Cursor x = db.rawQuery("SELECT DISTINCT Name FROM Movies ", null);
        List<String> nomes = new ArrayList<String>();
        
        if (x != null) {
            if (x.moveToFirst()) 
            {
                do 
                {
                    nomes.add(x.getString(x.getColumnIndex("Name")));
                } while (x.moveToNext());
            }
        }
        
        return nomes;
    }
    
    public List<String> getAllImages(String nome)
    {
        int idMovie = 0;
        
        Cursor id = db.rawQuery("SELECT DISTINCT idMovie FROM Movies WHERE Name = '" + nome + "'", null);
        if (id != null)
        {
            id.moveToFirst();
            idMovie = id.getInt(id.getColumnIndex("idMovie"));
        }
        
        List<String> Images = new ArrayList<String>();
        Cursor imgs = db.rawQuery("SELECT Image FROM Movies WHERE idMovie = " + idMovie, null);
        
        if (imgs != null) {
            if (imgs.moveToFirst()) 
            {
                do 
                {
                    Images.add(imgs.getString(imgs.getColumnIndex("Image")));
                } while (imgs.moveToNext());
            }
        }
        
        return Images;
    }
    
    public boolean checkIfInTop5(long score)
    {
        long lastScore = 0;
        
        Cursor top5 = db.rawQuery("SELECT Score FROM Scores ORDER BY Score DESC LIMIT 5", null);
        if (top5 != null)
        {
            if (top5.moveToLast())
                lastScore = top5.getColumnIndex("Score");
        }
        else
            return true;
        
        if (score > lastScore)
            return true;
        else
            return false;
    }
}